﻿using Vowei.Data.Converters.Excel;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using Vowei.Core.Models;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Vowei.Data.Models;
using Vowei.Data.Helpers;
using Vowei.Data;
using Vowei.Converters.Helpers;
using Vowei.Web.Controllers;
using Vowei.Web.Models;
using Vowei.Core;
using Vowei.Converters.Excel;

namespace vowei.test
{
    [TestClass()]
    public class ExcelUserConverterTest : TestBase
    {
        [TestMethod()]
        public void ConvertFromBasicSenarioTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromBasicSenarioTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new UserExcelConverter();
                IEnumerable<IUser> actual = target.ConvertFrom(stream);

                Assert.AreEqual(123, actual.Count());

                // 返回的结果列表的顺序应该与Excel一致
                Assert.AreEqual("施懿民", actual.ElementAt(0).DisplayName);
                Assert.AreEqual("yishi@vowei.com", actual.ElementAt(0).Email);

                var employee = actual.ElementAt(1) as Employee;
                Assert.IsNotNull(employee);

                var expectedManager = actual.ElementAt(0);
                Assert.AreEqual(expectedManager, employee.ReportTo);

                employee = actual.ElementAt(16) as Employee;
                Assert.IsNotNull(employee);
                var expectedReplacement = actual.ElementAt(120);
                Assert.AreEqual(expectedReplacement.Email, employee.Replacement.Email);
            }
        }

        [TestMethod()]
        public void ImportEnUserExcelInChineseEnvrionment()
        {
            // TODO: 这个测试用例在当前还不能通过，主要是在考虑是否有必要支持导入混合语种的Excel文件。
            //       当前的实现，主要是按照网站浏览者的区域偏好来推测导入的Excel的区域设置，也许在
            //       大部分的情况下，都是适用的，比如英国人应该会导入完全包含英文的Excel文件。
            //       但是，在另外一些国家，例如中国，在一个Excel文件里混用中英文的情况很常见，比如说
            //       有可能导入的Excel文件里，既有中文的列名（例如“直线经理”），也有英文的列名（例如“Email”）
            //       而英文的列名很多时候也会用中文表示，例如（Email也可以写成邮件地址）。
            // -----------------------------------------------------------------------------------------
            // 总的来说，我支持添加导入混合语种的Excel文件
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ImportEnUserExcelInChineseEnvrionment.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new UserExcelConverter();
                IEnumerable<IUser> actual = target.ConvertFrom(stream);

                Assert.AreEqual(123, actual.Count());

                // 返回的结果列表的顺序应该与Excel一致
                Assert.AreEqual("施懿民", actual.ElementAt(0).DisplayName);
                Assert.AreEqual("yishi@vowei.com", actual.ElementAt(0).Email);

                var employee = actual.ElementAt(1) as Employee;
                Assert.IsNotNull(employee);

                var expectedManager = actual.ElementAt(0);
                Assert.AreEqual(expectedManager, employee.ReportTo);

                employee = actual.ElementAt(16) as Employee;
                Assert.IsNotNull(employee);
                var expectedReplacement = actual.ElementAt(120);
                Assert.AreEqual(expectedReplacement.Email, employee.Replacement.Email);
            }
        }

        [TestMethod]
        public void ConvertFromRealScenarioTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromRealScenarioTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new UserExcelConverter();
                IEnumerable<IUser> actual = target.ConvertFrom(stream);

                Assert.AreEqual(54, actual.Count());

                // 返回的结果列表的顺序应该与Excel一致
                Assert.AreEqual("Yimin Shi", actual.ElementAt(0).DisplayName);
                Assert.AreEqual("yishi@vowei.com", actual.ElementAt(0).Email);

                var employee = actual.ElementAt(33) as Employee;
                Assert.IsNotNull(employee);

                Assert.AreEqual("Libai Chen", employee.ReportTo.DisplayName);

                employee = actual.ElementAt(13) as Employee;
                Assert.IsNotNull(employee);
                var expectedReplacement = actual.ElementAt(24);
                Assert.AreEqual(expectedReplacement.Email, employee.Replacement.Email);
            }
        }

        [TestMethod]
        public void ConvertToBasicTest()
        {
            var filename = "ConvertToBasicTest.xls";
            var expected = new List<IUser>();
            var target = new UserExcelConverter(new Employee()
            {
                DisplayName = "Test",
                Email = "test@vowei.com",
                Company = "vowei",
                Department = "Development",
                Title = "Tester",
            });
            target.TemplateExcel = Path.Combine(
                TestContext.TestDeploymentDir, @"ConvertToBasicTest_Template.xls");

            expected.Add(new Employee()
            {
                DisplayName = "CEO",
                Email = "ceo@vowei.com",
                OnboardDate = DateTime.Now,
                Department = "开发部",
                Level = "A1",
                Title = "CEO"
            });
            expected.Add(new Employee()
            {
                DisplayName = "CFO",
                Email = "cfo@vowei.com",
                OnboardDate = DateTime.Now,
                Department = "开发部",
                Level = "A1",
                Title = "CFO",
                ReportTo = (Employee)expected[0]
            });
            expected.Add(new Employee()
            {
                DisplayName = "new COO",
                Email = "newcoo@vowei.com",
                OnboardDate = DateTime.Now,
                Department = "开发部",
                Level = "A1",
                Title = "COO",
                ReportTo = (Employee)expected[0]
            });
            expected.Add(new Employee()
            {
                DisplayName = "COO",
                Email = "coo@vowei.com",
                OnboardDate = DateTime.Now,
                Department = "开发部",
                Level = "A1",
                Title = "COO",
                ReportTo = (Employee)expected[0],
                PotentialQuitDate = DateTime.Now + TimeSpan.FromDays(30),
                IsConfirmed = true,
                Replacement = (Employee)expected[2]
            });

            using (var stream = File.Open(filename, FileMode.CreateNew, FileAccess.Write))
            {
                target.ConvertTo(expected, stream);
            }

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var actual = target.ConvertFrom(stream);

                Assert.AreEqual(expected.Count, actual.Count());

                for (int i = 0; i < expected.Count; ++i)
                {
                    var e = expected[i] as Employee;
                    var a = actual.ElementAt(i) as Employee;
                    Assert.AreEqual(e.DisplayName, a.DisplayName);
                    Assert.AreEqual(e.Email, a.Email);
                    AreDateTimeEquals(e.OnboardDate, a.OnboardDate);

                    if (e.PotentialQuitDate.HasValue)
                    {
                        Assert.IsTrue(a.PotentialQuitDate.HasValue);
                        AreDateTimeEquals(e.PotentialQuitDate.Value, a.PotentialQuitDate.Value);
                    }
                    else
                    {
                        Assert.IsFalse(a.PotentialQuitDate.HasValue);
                    }

                    Assert.AreEqual(e.Department, a.Department);
                    Assert.AreEqual(e.Level, a.Level);
                    Assert.AreEqual(e.Title, a.Title);
                    Assert.AreEqual(e.IsConfirmed, a.IsConfirmed);

                    if (e.ReportTo != null)
                    {
                        Assert.IsNotNull(a.ReportTo);
                        Assert.AreEqual(e.ReportTo.Email, a.ReportTo.Email);
                    }
                    else
                    {
                        Assert.IsNull(a.ReportTo);
                    }

                    if (e.Replacement != null)
                    {
                        Assert.IsNotNull(a.Replacement);
                        Assert.AreEqual(e.Replacement.Email, a.Replacement.Email);
                    }
                    else
                    {
                        Assert.IsNull(a.Replacement);
                    }
                }
            }
        }

        [TestMethod]
        public void ConvertFromWbsFilesBasicTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromWbsFilesBasicTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                var milestones = actual.OfType<Milestone>();
                var requirements = actual.OfType<Requirement>();
                var tasks = actual.OfType<Task>();

                Assert.AreEqual(25, actual.Count());
                Assert.AreEqual(3, milestones.Count());
                Assert.AreEqual(6, requirements.Count());
                Assert.AreEqual(16, tasks.Count());

                Assert.AreEqual("项目管理基本理念预览", milestones.First().Title);
                Assert.AreEqual("需求管理基本功能", requirements.First().Title);
                Assert.AreEqual("架构需求管理的数据库结构", tasks.First().Title);
                Assert.AreEqual("项目结束", milestones.Last().Title);

                Assert.AreEqual(4, requirements.Where(r => r.BelongsToMilestone.ID == milestones.First().ID).Count());
                Assert.AreEqual(2, requirements.First().Children.OfType<Requirement>().Count());
                Assert.AreEqual(7, requirements.First().Children.OfType<Task>().Count());
            }
        }

        [TestMethod]
        public void ConvertFromWbsFilesRealSenarioTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromWbsFilesRealSenarioTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                var milestones = actual.OfType<Milestone>();
                var requirements = actual.OfType<Requirement>();
                var tasks = actual.OfType<Task>();

                Assert.AreEqual(135, actual.Count());
                Assert.AreEqual(6, milestones.Count());
                Assert.AreEqual(33, requirements.Count());
                Assert.AreEqual(96, tasks.Count());

                Assert.AreEqual("项目管理基本理念预览", milestones.First().Title);
                Assert.AreEqual("需求管理基本功能", requirements.First().Title);
                Assert.AreEqual("架构需求管理的数据库结构", tasks.First().Title);
                Assert.AreEqual("项目结束", milestones.Last().Title);

                Assert.AreEqual(14, requirements.Where(r => r.BelongsToMilestone.ID == milestones.First().ID).Count());
                Assert.AreEqual(2, requirements.First().Children.OfType<Requirement>().Count());
                Assert.AreEqual(8, requirements.First().Children.OfType<Task>().Count());

                var milestone = milestones.First();
                var project = CreateProjectHelper("ConvertFromWbsFilesRealSenarioTest");
                ImportHelper.ImportDataTo(project, VoweiContext, actual, Setting.DefaultFormUser);
                requirements = project.AllRequirements(VoweiContext);
                Assert.AreEqual(14, requirements.Where(r => r.BelongsToMilestoneId == milestone.ID).Count());
            }
        }

        [TestMethod]
        public void WorkItemsConvertToRealScenarioTest()
        {
            var template = Path.Combine(
                   TestContext.TestDeploymentDir, @"WorkItemsConvertToRealScenarioTest_Template.xls");
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"WorkItemsConvertToRealScenarioTest_Output.xls");

            using (var stream = File.Open(filename, FileMode.CreateNew, FileAccess.Write))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                target.TemplateExcel = template;
                var works = new List<ITable>();
                using ( var impl = new VoweiContextImpl() )
                {
                    var project = GetDefaultProject(impl);
                    var context = new VoweiContext(impl);
                    works.Add(project);
                    foreach (var milestone in project.Children)
                    {
                        works.Add(milestone);
                        works.AddRange(milestone.AllRequirements(context));
                    }

                    target.ConvertTo(works, stream);
                }
            }
        }

        [TestMethod]
        public void WorkItemsConvertToConvertFromCombinedTest()
        {
            var template = Path.Combine(
                   TestContext.TestDeploymentDir, @"WorkItemsConvertToRealScenarioTest_Template.xls");
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"WorkItemsConvertToConvertFromCombinedTest_Output.xls");
            Project expected = null;

            // 转换到Excel
            using (var stream = File.Open(filename, FileMode.CreateNew, FileAccess.Write))
            {
                using (var impl = new VoweiContextImpl())
                {
                    var context = new VoweiContext(impl);
                    expected = GetDefaultProject(impl);
                    expected.ConvertToExcel(context, stream, template);
                }
            }
            
            // 读取回来
            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                var milestones = actual.OfType<Milestone>();
                var requirements = actual.OfType<Requirement>();
                var tasks = actual.OfType<Task>();

                using (var impl = new VoweiContextImpl())
                {
                    var context = new VoweiContext(impl);
                    var expectedMilestones = expected.AllMilestones(context);
                    var expectedReqs = expected.AllRequirements(context);
                    var expectedTasks = expected.AllTickets(context).OfType<Task>();

                    // TODO: 这是因为生成的Excel文件里的公式,还没有被Excel打开过
                    // 因此在读取的时候,就读不到Excel在文件里缓存的公式的计算值,而在程序
                    // 里再次读取的时候,读取不到缓存的值,而误认为是里程碑
                    Assert.AreEqual(42, milestones.Count());
                    Assert.AreEqual(0, requirements.Count());
                    Assert.AreEqual(0, tasks.Count());
                }
            }
        }
        
        [TestMethod]
        public void ConvertFromWbsFilesRealSenarioTaskAssignedByUserNameTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromWbsFilesRealSenarioTaskAssignedByUserNameTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                var milestones = actual.OfType<Milestone>();
                var requirements = actual.OfType<Requirement>();
                var tasks = actual.OfType<Task>();

                Assert.AreEqual(135, actual.Count());
                Assert.AreEqual(6, milestones.Count());
                Assert.AreEqual(33, requirements.Count());
                Assert.AreEqual(96, tasks.Count());

                Assert.AreEqual("项目管理基本理念预览", milestones.First().Title);
                Assert.AreEqual("需求管理基本功能", requirements.First().Title);
                Assert.AreEqual("架构需求管理的数据库结构", tasks.First().Title);
                Assert.AreEqual("项目结束", milestones.Last().Title);

                Assert.AreEqual(14, requirements.Where(r => r.BelongsToMilestone.ID == milestones.First().ID).Count());
                Assert.AreEqual(2, requirements.First().Children.OfType<Requirement>().Count());
                Assert.AreEqual(8, requirements.First().Children.OfType<Task>().Count());

                Assert.IsTrue(tasks.Any(t => t.AssignedTo == @"testdomain\username"));
            }
        }

        [TestMethod]
        public void ImportUserFromImportUserTemplateExcel()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"import user template.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new UserExcelConverter();
                IEnumerable<IUser> actual = target.ConvertFrom(stream);

                Assert.AreEqual(2, actual.Count());

                // 返回的结果列表的顺序应该与Excel一致
                Assert.AreEqual("Manager", actual.ElementAt(0).DisplayName);
                Assert.AreEqual("manager@vowei.com", actual.ElementAt(0).Email);

                var employee = actual.ElementAt(1) as Employee;
                Assert.IsNotNull(employee);

                var expectedManager = actual.ElementAt(0);
                Assert.AreEqual(expectedManager, employee.ReportTo);
            }
        }

        [TestMethod]
        public void ImportUserWithMinimumRequiredDataTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ImportUserWithMinimumRequiredDataTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new UserExcelConverter();
                IEnumerable<IUser> actual = target.ConvertFrom(stream);

                Assert.AreEqual(2, actual.Count());

                // 返回的结果列表的顺序应该与Excel一致
                Assert.AreEqual("Manager", actual.ElementAt(0).DisplayName);
                Assert.AreEqual("manager@vowei.com", actual.ElementAt(0).Email);

                var employee = actual.ElementAt(1) as Employee;
                Assert.IsNotNull(employee);

                var expectedManager = actual.ElementAt(0);
                Assert.AreEqual(expectedManager, employee.ReportTo);
            }
        }

        [TestMethod]
        public void ConvertFromRealScenarioByOnlyUserNameTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromRealScenarioByOnlyUserNameTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new UserExcelConverter();
                IEnumerable<IUser> actual = target.ConvertFrom(stream);

                Assert.AreEqual(54, actual.Count());

                // 返回的结果列表的顺序应该与Excel一致
                Assert.AreEqual("Yimin Shi", actual.ElementAt(0).DisplayName);
                Assert.AreEqual(@"vowei\yishi", actual.ElementAt(0).UserName);

                var employee = actual.ElementAt(33) as Employee;
                Assert.IsNotNull(employee);

                Assert.AreEqual("Libai Chen", employee.ReportTo.DisplayName);

                employee = actual.ElementAt(13) as Employee;
                Assert.IsNotNull(employee);
                var expectedReplacement = actual.ElementAt(24);
                Assert.AreEqual(expectedReplacement.UserName, employee.Replacement.UserName);
            }
        }

        [TestMethod]
        public void ConvertFromUserAddTasksThroughModifyingTemplateTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ConvertFromUserAddTasksThroughModifyingTemplateTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                target.WorkSheetName = "Task Template";
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                var milestones = actual.OfType<Milestone>();
                var requirements = actual.OfType<Requirement>();
                var tasks = actual.OfType<Task>();

                Assert.AreEqual(12, actual.Count());
                Assert.AreEqual(1, milestones.Count());
                Assert.AreEqual(6, requirements.Count());
                Assert.AreEqual(5, tasks.Count());

                Assert.AreEqual("Requirement Analysis", milestones.First().Title);
                Assert.AreEqual("Requirement documentation", requirements.First().Title);
                Assert.AreEqual("review requirement", tasks.First().Title);

                Assert.AreEqual(6, requirements.Where(r => r.BelongsToMilestone.ID == milestones.First().ID).Count());
                Assert.AreEqual(1, requirements.First().Children.OfType<Requirement>().Count());

                Assert.IsTrue(tasks.Any(t => t.AssignedTo == @"yimin_shi@hotmail.com"));
            }
        }

        private Task CreateTaskHelper(string name, Milestone milestone, Guid id)
        {
            var result = CreateTaskHelper(name, milestone);
            result.ID = id;
            return result;
        }

        private Requirement CreateRequirementHelper(string name, Milestone milestone, Guid id)
        {
            var result = CreateRequirementHelper(name, milestone);
            result.ID = id;
            return result;
        }

        [TestMethod]
        public void UpdateWorkItesmFromExcelDataConnectionBasicTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_UpdateWorkItesmFromExcelDataConnectionBasicTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new DataConnectionWorkItemExcelConverter(VoweiContext.TicketStatusManager);
                // 不指明包含任务的Excel WorkSheet的话，就采用默认第一个WorkSheet作为饱含任务的工作表
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                Assert.AreEqual(10, actual.Count());

                var id = new Guid("00000000-0000-0000-0001-000000000001");
                Assert.IsTrue(actual.Any(t => t.ID == id));

                var newTask = actual.ElementAt(1) as Task;
                Assert.IsNotNull(newTask);
                Assert.AreEqual("New Task 1", newTask.Title);

                var newReq = actual.ElementAt(7) as Requirement;
                Assert.IsNotNull(newReq);
                Assert.AreEqual("New Requirement 1", newReq.Title);
            }
        }

        [TestMethod]
        public void UpdateWorkItesmFromExcelScenarioTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_UpdateWorkItesmFromExcelDataConnectionBasicTest.xls");
            var project = CreateProjectHelper("UpdateWorkItesmFromExcelScenarioTest");
            var updatableTickets = new Ticket[] 
            {
                CreateTaskHelper("Task 1", project, new Guid("00000000-0000-0000-0001-000000000001")),
                CreateTaskHelper("Task 2", project, new Guid("00000000-0000-0000-0001-000000000002")),
                CreateTaskHelper("Task 3", project, new Guid("00000000-0000-0000-0001-000000000003")),
                CreateRequirementHelper("Requirement 1", project, new Guid("00000000-0000-0000-0002-000000000001")),
                CreateRequirementHelper("Requirement 2", project, new Guid("00000000-0000-0000-0002-000000000002")),
                CreateRequirementHelper("Requirement 3", project, new Guid("00000000-0000-0000-0002-000000000003")),
            };

            using (var impl = new VoweiContextImpl())
            {
                try
                {
                    var context = new VoweiContext(impl);
                    foreach (var ticket in updatableTickets) 
                        impl.Tickets.Add(ticket);

                    impl.SaveChanges();

                    using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
                    {
                        var target = new DataConnectionWorkItemExcelConverter(VoweiContext.TicketStatusManager);
                        // 不指明包含任务的Excel WorkSheet的话，就采用默认第一个WorkSheet作为饱含任务的工作表
                        IEnumerable<ITable> actual = target.ConvertFrom(stream);

                        ImportHelper.ImportDataTo(project, context, actual, Setting.DefaultFormUser);
                    }

                    var tickets = project.AllTickets(context);
                    Assert.IsTrue(tickets.OfType<Task>().Any(t => t.Title == "New Task 1"));
                    Assert.IsTrue(tickets.OfType<Task>().Any(t => t.Title == "New Task 1"));
                    Assert.IsTrue(tickets.OfType<Task>().Any(t => t.Title == "Not exists task 1"));
                    Assert.IsTrue(tickets.OfType<Requirement>().Any(t => t.Title == "Not exists Requirement 1"));

                    var verifyTask = context.Tasks.Find(new Guid("00000000-0000-0000-0001-000000000001"));
                    Assert.AreEqual("Updated Task 1 Title", verifyTask.Title);
                    AreDateTimeEquals(new DateTime(2010, 12, 30, 10, 0, 0), verifyTask.StartDate);
                    AreDateTimeEquals(new DateTime(2011, 1, 3, 10, 0, 0), verifyTask.DueDate);
                    Assert.AreEqual("HW-YISH-2\\Administrator", verifyTask.AssignedTo);

                    verifyTask = context.Tasks.Find(new Guid("00000000-0000-0000-0001-000000000002"));
                    Assert.AreEqual("更新过的Task 2的标题", verifyTask.Title);
                    AreDateTimeEquals(new DateTime(2011, 3, 8, 0, 0, 0), verifyTask.StartDate);
                    AreDateTimeEquals(new DateTime(2011, 3, 8, 1, 0, 0), verifyTask.DueDate);
                    Assert.AreEqual("HW-YISH-2\\Administrator", verifyTask.AssignedTo);

                    var verifyReq = context.Requirements.Find(new Guid("00000000-0000-0000-0002-000000000002"));
                    Assert.AreEqual("Updated Requirement 2", verifyReq.Title);
                    Assert.AreEqual("HW-YISH-2\\Administrator", verifyReq.AssignedTo);
                }
                finally
                {
                    foreach (var ticket in updatableTickets)
                    {
                        if (impl.Tickets.Any(t => t.ID == ticket.ID))
                            impl.Tickets.Remove(ticket);
                    }

                    impl.Projects.Remove(project);
                    impl.SaveChanges();
                }
            }

        }
        
        [TestMethod]
        public void ConvertFromExcelWithAllFieldsTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"statistics testing data.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var target = new WorkItemExcelConverter(VoweiContext.TicketStatusManager);
                target.WorkSheetName = "TaskSchedule";
                IEnumerable<ITable> actual = target.ConvertFrom(stream);

                var milestones = actual.OfType<Milestone>();
                var requirements = actual.OfType<Requirement>();
                var tasks = actual.OfType<Task>();

                var requirement = requirements.First(r => r.Title == "需求管理基本功能");
                Assert.AreEqual(10000, requirement.BusinessValue);
                requirement = requirements.First(r => r.Title == "与Excel项目管理模板集成");
                Assert.AreEqual("yimin_shi@hotmail.com", requirement.AssignedTo);

                var task = tasks.First(t => t.Title == "设计Excel项目管理模板");
                AreDateTimeEquals(new DateTime(2011, 2, 1, 10, 0, 0), task.StartDate);
                AreDateTimeEquals(new DateTime(2011, 1, 15, 0, 0, 0), task.ClosedDate);
                AreDateTimeEquals(new DateTime(2010, 12, 7, 0, 0, 0), task.OpenDate);

                requirement = requirements.First(r => r.Title == "统计功能");
                Assert.AreEqual(30000, requirement.BusinessValue);

                requirement = requirements.First(r => r.Title == "建立任务与Outlook同步的机制");
                AreDateTimeEquals(DateTime.Now, requirement.ClosedDate);

                task = tasks.First(t => t.Title == "构建任务增改页面");
                AreDateTimeEquals(DateTime.Now, task.ClosedDate);
            }
        }

        [TestMethod]
        public void ImportProjectMemberAndAssignmentTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_ImportProjectMemberAndAssignmentTest.xls");

            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var uec = new UserExcelConverter();
                var target = new AssignmentExcelConverter(uec);
                var actual = target.ConvertFrom(stream);

                Assert.AreEqual(8, actual.Count());
                var first = actual.First();
                Assert.AreEqual("施懿民", first.Employee.DisplayName);
                Assert.AreEqual("yishi@vowei.com", first.Employee.Email);
                Assert.AreEqual(12.0f, first.Assignment.CostPerHour);
            }
        }

        [TestMethod]
        public void BVT_ResourceConverterConvertFromTest()
        {
            var filename = Path.Combine(
                TestContext.TestDeploymentDir, @"ExcelUserConverterTest_BVT_ResourceConverterConvertFromTest.xls");
            
            using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                var converter = new ResourceExcelConverter();
                converter.WorkSheetName = "Host machine";
                var actual = converter.ConvertFrom(stream);

                Assert.AreEqual(142, actual.Count());
                var first = actual.First();
                Assert.AreEqual("01ACP01524", first.Title);
                Assert.IsTrue(first.Description.Contains("S/N"));
                Assert.IsTrue(first.Description.Contains("Comments"));
                Assert.IsTrue(first.Description.Contains("Owner"));
                Assert.IsTrue(first.Description.Contains("陈利柏"));
            }
        }
    }
}
